import os
import sys

sys.path.append(os.path.dirname(__file__))
import socket
import subprocess
import time
import requests
from sqlalchemy import create_engine, text
from db_config import DATABASE_URL

FASTAPI_PORT = 8080
LOG_PATH = 'text2sql_fastapi/uvicorn.log'


# 检查端口是否被占用
def is_port_in_use(port):
    with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
        return s.connect_ex(('127.0.0.1', port)) == 0


# 启动 FastAPI 服务
def start_server():
    if os.path.exists(LOG_PATH):
        os.remove(LOG_PATH)
    # 新窗口启动
    if os.name == 'nt':
        subprocess.Popen(
            f'start cmd /k "uvicorn text2sql_fastapi.main:app --reload --port {FASTAPI_PORT} > {LOG_PATH} 2>&1"',
            shell=True)
    else:
        subprocess.Popen(f'uvicorn text2sql_fastapi.main:app --reload --port {FASTAPI_PORT} > {LOG_PATH} 2>&1 &',
                         shell=True)


# 健康检查
def wait_for_server(timeout=30):
    url = f'http://127.0.0.1:{FASTAPI_PORT}/docs'
    for _ in range(timeout):
        try:
            r = requests.get(url, timeout=2)
            if r.status_code == 200:
                return True
        except Exception:
            pass
        time.sleep(1)
    return False


# 自动建表和插入测试数据
def init_db():
    engine = create_engine(DATABASE_URL)
    with engine.connect() as conn:
        # 创建 student 表
        conn.execute(text('''
            CREATE TABLE IF NOT EXISTS student (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(50),
                age INT
            )
        '''))
        # 创建 score 表
        conn.execute(text('''
            CREATE TABLE IF NOT EXISTS score (
                id INT PRIMARY KEY AUTO_INCREMENT,
                student_id INT,
                score INT
            )
        '''))
        # 插入测试数据
        conn.execute(text("DELETE FROM student"))
        conn.execute(text("DELETE FROM score"))
        conn.execute(text("INSERT INTO student (name, age) VALUES ('张三', 20), ('李四', 21), ('王五', 22)"))
        conn.execute(text("INSERT INTO score (student_id, score) VALUES (1, 95), (2, 88), (3, 76)"))
        conn.commit()
    print("数据库表和测试数据已初始化")


if __name__ == "__main__":
    # 1. 检查端口
    if is_port_in_use(FASTAPI_PORT):
        print(f"端口 {FASTAPI_PORT} 已被占用，尝试在新窗口启动服务...")
        FASTAPI_PORT2 = 8081
        if not is_port_in_use(FASTAPI_PORT2):
            FASTAPI_PORT = FASTAPI_PORT2
        else:
            raise RuntimeError("8080/8081 都被占用，请手动释放端口")
    # 2. 启动服务
    start_server()
    print(f"正在启动 FastAPI 服务，端口: {FASTAPI_PORT}")
    # 3. 健康检查
    if wait_for_server():
        print("服务已启动并通过健康检查")
    else:
        print("服务启动失败，请检查日志：", LOG_PATH)
        exit(1)
    # 4. 初始化数据库
    init_db()
    print("自动化流程完成，可运行自动化测试脚本验证功能。")
